package com.example.demo.data;

import com.example.demo.service.CoreService;
import com.example.demo.service.DataQueryService;
import com.example.demo.vo.Boutlist;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * Created by ltian on 2020/1/16.
 */
@Slf4j
@Component
public class TradeDao {

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    protected JdbcTemplate jdbcTemplate;
    @Autowired
    private CoreService coreService;
    @Autowired
    private DataQueryService dataQueryService;

    @Autowired
    private SxConfig config;
    private static final int BATCH_SIZE = 100;

    public void doTask(int squaddate) {
        //final int totalCount = getBoutlistTotal(squaddate);
        int scanCount = 0;
        String offsetNo = "0";
        while (true) {
            List<Boutlist> boutListRows = queryBoutList(squaddate, offsetNo, BATCH_SIZE);
            scanCount += boutListRows.size();
            //log.info("scan boutlist, squaddate: {}, count: {}/{}", squaddate, totalCount, scanCount);
            log.info("scan boutlist, squaddate: {}, handled count: {}", squaddate,  scanCount);
            if (boutListRows.isEmpty()) {
                log.info("done, squaddate:{}", squaddate);
                break;
            }
            offsetNo = boutListRows.get(boutListRows.size() - 1).getListno();

            int total=0;
            for (Boutlist bout : boutListRows) {
                boolean isUpdated=false;
                try {
                    String listno = bout.getListno();
                    String elecardno = bout.getElecardno();
                    String elecardid = bout.getElecardid();
                    String entolllaneid = bout.getEntolllaneid();
                    String entolllanehex = bout.getEntolllanehex();
                    String vehplate = bout.getVehplate();    // 出口车牌
                    String invehplate = bout.getInvehplate();//入口车牌
                    String vehplateauto = bout.getVehplateauto();//识别车牌
                    String eleobuid = bout.getEleobuid();
                    String passid = bout.getPassid();
                    Integer obusign = bout.getObusign();
                    Integer signstatus = bout.getSignstatus();
                    String transtype = bout.getTranstype();
                    Integer vehclass = bout.getVehclass();
                    Integer vehtype = bout.getVehtype();
                    Integer invehtype = bout.getInvehtype();
                    Integer vehtypeauto = bout.getVehtypeauto();

                    //4
                    if (listno.length() != 37) {
                        log.warn("trade, bad listno， {}", bout.getListno());
                        coreService.abort(bout);
                        continue;
                    }
                    //1
                    if (StringUtils.isEmpty(elecardno) && StringUtils.isEmpty(elecardid)) {
                        log.warn("trade, bad elecardno， {}", bout.getListno());
                        coreService.abort(bout);
                        continue;
                    }
                    if (StringUtils.isEmpty(elecardno) || elecardno.length() != 20) {
                        if (StringUtils.isEmpty(elecardid) || elecardid.length() != 20) {
                            log.warn("trade, bad elecardno， {}", bout.getListno());
                            coreService.abort(bout);
                            continue;
                        }
                        elecardno = elecardid;
                    }
                    //2
                    if (bout.getEntolllaneid() == null || bout.getEntolllaneid().length() < 21) {
                        if (bout.getEntolllanehex() == null) {
                            log.warn("blank entolllanehex, lineno:{}, entolllanehex:{}", bout.getListno(), bout.getEntolllanehex());
                            coreService.abort(bout);
                            continue;
                        }

                        if (bout.getEntolllanehex().length() == 10) {
                            // 取车道号
                            entolllaneid = dataQueryService.queryLaneId(bout.getEntolllanehex());
                            if (entolllaneid == null || entolllaneid.length() < 21) {
                                String stationId = dataQueryService.queryStationId(bout.getEntolllanehex().substring(0, 8));
                                if (stationId == null || stationId.length() != 14) {
                                    log.warn("====>  found invalid stationId from basicInfoHex, listno: {}, Entolllanehex: {}", bout.getListno(), bout.getEntolllanehex());
                                    coreService.abort(bout);
                                    continue;
                                }
                                entolllaneid = stationId + "2020010";
                            }


                        } else if (bout.getEntolllanehex().length() == 8) {
                            // 根据车站号取最后一个车道号和10位的hex
                            Pair<String, String> pair = dataQueryService.queryLandIdAndHex(bout.getEntolllanehex());
                            if (pair == null) {
                                log.warn("can not find basicinfo from hex8, listno:{}", bout.getListno());
                                coreService.abort(bout);
                                continue;
                            }

                            entolllaneid = pair.getLeft();
                            entolllanehex = pair.getRight(); //10位的hex
                            if (entolllaneid == null || entolllaneid.length() < 21) {
                                entolllaneid = dataQueryService.queryEnLandId(1, bout.getEntolllanehex().substring(0, 8));//stationId
                                if (entolllaneid == null || entolllaneid.length() != 14) {
                                    log.warn("====>  found invalid id from basicInfoHex, listno: {}, Entolllanehex: {}", bout.getListno(), bout.getEntolllanehex());
                                    coreService.abort(bout);
                                    continue;
                                }
                                entolllaneid = entolllaneid + "2020010";
                            }

                        } else {
                            log.warn("bad Entolllanehex, len not in [8,10] listno:{}, hex:{}", bout.getListno(), bout.getEntolllanehex());
                            coreService.abort(bout);
                            continue;
                        }

                    }
                    if (StringUtils.isEmpty(entolllaneid) || entolllaneid.length() != 21) {
                        entolllaneid = "0";
                    }
                    //3
                    if (StringUtils.isEmpty(entolllanehex) || entolllanehex.length() != 10) {
                        log.warn("trade, bad entolllanehex， {}", bout.getListno());
                        coreService.abort(bout);
                        continue;
                    }

                    //5 11
                    if (!coreService.isValidatePlate(vehplate)) {
                        if (!coreService.isValidatePlate(invehplate)) {
                            log.warn("invalid vahicle plate, in:{}, out:{}, listno:{}", invehplate, vehplate, bout.getListno());
                            coreService.abort(bout);
                            continue;
                        } else {
                            vehplate = invehplate;
                        }
                    } else if (!coreService.isValidatePlate(invehplate)) {
                        invehplate = vehplate;
                    }

                    if (!coreService.isValidatePlate(vehplateauto) || vehplateauto.equals("*****")) {
                        vehplateauto = vehplate;
                    }
                    //6
                    if (StringUtils.isEmpty(eleobuid) || eleobuid.length() != 16) {
                        if (eleobuid.length() < 16) {
                            log.warn("trade, bad eleobuid， {}", bout.getListno());
                            coreService.abort(bout);
                            continue;
                        } else {
                            eleobuid = eleobuid.substring(0, 16);
                            if(StringUtils.isEmpty(passid)||passid.length()<14) {
                                log.warn("trade, bad passid， {}", bout.getListno());
                                coreService.abort(bout);
                                continue;
                            }
                            passid = eleobuid + passid.substring(passid.length() - 14, passid.length());
                        }
                    }
                    //7
                    if (ObjectUtils.isEmpty(obusign) || (obusign != 1 && obusign != 2)) {
                        obusign = 2;
                    }
                    //8
                    if (ObjectUtils.isEmpty(signstatus) || (signstatus != 1 && signstatus != 2)) {
                        signstatus = 1;
                    }
                    //9
                    if (StringUtils.isEmpty(transtype) || !"09".equals(transtype)) {
                        transtype = "09";
                    }
                    //10
                    if (ObjectUtils.isEmpty(vehclass) || vehclass == 1) {
                        vehclass = 0;
                    }
                    //12
                    String vehtypeStr = "1,2,3,4,11,12,13,14,15,16,21,22,23,24,25,26";
                    if (ObjectUtils.isEmpty(vehtype) || !vehtypeStr.contains(vehtype.toString())) {
                        if (ObjectUtils.isEmpty(invehtype) || !vehtypeStr.contains(invehtype.toString())) {
                            if (ObjectUtils.isEmpty(vehtypeauto) || !vehtypeStr.contains(vehtypeauto.toString())) {
                                log.warn("trade, bad eleobuid， {}", bout.getListno());
                                coreService.abort(bout);
                                continue;
                            } else {
                                vehtype = vehtypeauto;
                                invehtype = vehtypeauto;
                            }
                        } else {
                            vehtype = invehtype;
                        }
                    }
                   int singleCount= updateBou(listno, elecardno, elecardid, entolllaneid, entolllanehex, vehplate, invehplate, vehplateauto, eleobuid, passid, obusign, signstatus, transtype, vehclass, vehtype, invehtype, vehtypeauto);
                    if(singleCount>0) {
                        isUpdated=true;
                    }
                   total+=singleCount;
                    log.info("updated:{},listno {}", isUpdated,listno);
                } catch (Exception e) {
                    log.error("error:, listno:" + bout.getListno(), e);
                }
            }
        }
    }

    private int getBoutlistTotal(int squaddate) {
        String sql = "SELECT count(*) FROM " + config.getBoutlist() +
                " WHERE squaddate=? and ctranspackname<>'999' and vehclass in(0,1) and totaltoll=0 and ctranspackname not in (select packname from clear_pack_muletc_main where tablename = '" + config.getBoutlist() + "' and datatype =25 and upstatus = 1)";
        return jdbcTemplate.queryForObject(sql, new Object[]{squaddate}, Integer.class);
    }

    private List<Boutlist> queryBoutList(int squaddate, String offsetNo, int limit) {
//        String sql = "select * from etcboutlist_tmp_20200117 where squaddate=20200115 and vehclass in(0,1) and totaltoll=0 and ctranspackname not in (select packname from clear_pack_muletc_main where tablename = 'etcboutlist_tmp_20200117' and datatype =25 and upstatus = 1) and ctranspackname<>'999' and listno='S007514068004020200102020011513000028'";
//        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Boutlist.class));
        String sql = "SELECT * FROM (" +
                "SELECT * FROM " + config.getBoutlist() +
                " WHERE listno > ? and ctranspackname<>'999' and squaddate=?  and vehclass in(0,1) and totaltoll=0 and ctranspackname not in (select packname from clear_pack_muletc_main where tablename = '" + config.getBoutlist() + "' and datatype = 25 and upstatus = 1) ORDER BY listno ASC" +
                ") WHERE rownum <=?";
        return jdbcTemplate.query(sql, new Object[]{offsetNo, squaddate, limit}, new BeanPropertyRowMapper<>(Boutlist.class));
    }

    private int updateBou(String listno,
                          String elecardno,
                          String elecardid,
                          String entolllaneid,
                          String entolllanehex,
                          String vehplate,
                          String invehplate,
                          String vehplateauto,
                          String eleobuid,
                          String passid,
                          Integer obusign,
                          Integer signstatus,
                          String transtype,
                          Integer vehclass,
                          Integer vehtype,
                          Integer invehtype,
                          Integer vehtypeauto) {
        String updateSql = "UPDATE " + config.getBoutlist() +
                " SET elecardno=?,elecardid=?,entolllaneid=?,entolllanehex=?,vehplate=?,invehplate=?,vehplateauto=?,eleobuid=?,passid=?,obusign=?,signstatus=?,transtype=?,vehclass=?,vehtype=?,invehtype=?,vehtypeauto=? WHERE listno=?";

        if (config.isReadonly()) {
            return 0;
        }

        return jdbcTemplate.update(updateSql, elecardno, elecardid, entolllaneid, entolllanehex, vehplate, invehplate, vehplateauto, eleobuid, passid, obusign, signstatus, transtype, vehclass, vehtype, invehtype, vehtypeauto, listno);
    }

}
